﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SEO.Entity;
using System.Data.SqlClient;
using System.Data;

namespace SEO.Business
{
    /// <summary>
    /// 关键字操作
    /// </summary>
    public class BisKeywords
    {
        /// <summary>
        /// 获取关键字
        /// </summary>
        /// <param name="nID">ID</param>
        /// <returns></returns>
        public Keywords Get(int nID)
        {
            SqlDataReader dr = SqlHelper.ExecuteReader(
                                                         SqlHelper.database,
                                                         CommandType.Text,
                                                         "select top 1 * from [Keywords] where [ID]=@ID",
                                                         new SqlParameter("@ID", nID)
                                                         );
            Keywords Info = null;
            while (dr.Read())
            {
                Info = new Keywords();
                Info.ID = Convert.ToInt32(dr["ID"]);
                Info.SortID = Convert.ToInt32(dr["SortID"]);
                Info.Scale = Convert.ToDouble(dr["Scale"]);
                Info.SysLock = Convert.ToBoolean(dr["SysLock"]);
                Info.DiyLock = Convert.ToBoolean(dr["DiyLock"]);
                Info.Name = dr["Name"].ToString();
                Info.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            }
            dr.Close();
            return Info;
        }

        /// <summary>
        /// 新增关键字
        /// </summary>
        /// <param name="Info"></param>
        /// <returns></returns>
        public bool Insert(Keywords Info)
        {
            string strSql = "insert into [Keywords]([SortID],[Name],[Scale]) values(@SortID,@Name,@Scale)";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SortID", Info.SortID));
            parms.Add(new SqlParameter("@Name", Info.Name));
            parms.Add(new SqlParameter("@Scale", Info.Scale));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 更新关键字
        /// </summary>
        /// <param name="Info"></param>
        /// <returns></returns>
        public bool Update(Keywords Info)
        {
            string strSql = "update [Keywords] set [SortID]=@SortID,[Name]=@Name,[Scale]=@Scale where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SortID", Info.SortID));
            parms.Add(new SqlParameter("@Name", Info.Name));
            parms.Add(new SqlParameter("@Scale", Info.Scale));
            parms.Add(new SqlParameter("@ID", Info.ID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 更改系统锁定状态
        /// </summary>
        /// <returns></returns>
        public bool UpdateAllSysLock(bool bSysLock)
        {
            string strSql = "update [Keywords] set [SysLock]=@SysLock";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SysLock", bSysLock));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 更改系统锁定状态
        /// </summary>
        /// <returns></returns>
        public bool UpdateSysLock(int nID,bool bSysLock)
        {
            string strSql = "update [Keywords] set [SysLock]=@SysLock where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SysLock", bSysLock));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 更改自定义锁定状态
        /// </summary>
        /// <returns></returns>
        public bool UpdateDiyLock(int nID, bool bDiyLock)
        {
            string strSql = "update [Keywords] set [DiyLock]=@DiyLock where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@DiyLock", bDiyLock));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 删除关键字
        /// </summary>
        /// <param name="nID">ID</param>
        /// <returns></returns>
        public bool Delete(int nID)
        {
            return SqlHelper.ExecuteNonQuery(
                   SqlHelper.database,
                   CommandType.StoredProcedure,
                   "DeleteKeywords",
                   new SqlParameter("@ID", nID)) > 0;
        }

        /// <summary>
        /// 获取关键字列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetList(string strName, int nSortID,int nSysLock,int nDiyLock, int nPageIndex, int nPageSize, ref int nPageCount, ref int nRecordCount)
        {
            SqlParameter[] parms = new SqlParameter[8];
            parms[0] = new SqlParameter("@Name", strName);
            parms[1] = new SqlParameter("@SortID", nSortID);
            parms[2] = new SqlParameter("@SysLock", nSysLock);
            parms[3] = new SqlParameter("@DiyLock", nDiyLock);
            parms[4] = new SqlParameter("@PageIndex", nPageIndex);
            parms[5] = new SqlParameter("@PageSize", nPageSize);

            //返回值
            parms[6] = new SqlParameter("@PageCount", nPageCount);
            parms[6].Direction = ParameterDirection.Output;
            parms[7] = new SqlParameter("@RecordCount", nRecordCount);
            parms[7].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.ExecuteDataset(
                SqlHelper.database,
                CommandType.StoredProcedure,
                "GetKeywordsList",
                parms).Tables[0];
            nPageCount = Convert.ToInt32(parms[6].Value);
            nRecordCount = Convert.ToInt32(parms[7].Value);
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="nSortID"></param>
        /// <param name="nSysLock"></param>
        /// <param name="nDiyLock"></param>
        /// <param name="nPageIndex"></param>
        /// <param name="nPageSize"></param>
        /// <param name="nPageCount"></param>
        /// <param name="nRecordCount"></param>
        /// <returns></returns>
        public DataTable GetArticleKeywordList(string strName, int nSortID, int nSysLock, int nDiyLock, int nPageIndex, int nPageSize, ref int nPageCount, ref int nRecordCount)
        {
            return null;
        }

        /// <summary>
        /// 获取今日关键字列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTodayList(string strName, int nSortID, DateTime dBeginTime, DateTime dEndTime,int nPageIndex, int nPageSize, ref int nPageCount, ref int nRecordCount)
        {
            SqlParameter[] parms = new SqlParameter[8];
            parms[0] = new SqlParameter("@Name", strName);
            parms[1] = new SqlParameter("@SortID", nSortID);
            parms[2] = new SqlParameter("@BeginTime", dBeginTime);
            parms[3] = new SqlParameter("@EndTime", dEndTime);
            parms[4] = new SqlParameter("@PageIndex", nPageIndex);
            parms[5] = new SqlParameter("@PageSize", nPageSize);

            //返回值
            parms[6] = new SqlParameter("@PageCount", nPageCount);
            parms[6].Direction = ParameterDirection.Output;
            parms[7] = new SqlParameter("@RecordCount", nRecordCount);
            parms[7].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.ExecuteDataset(
                SqlHelper.database,
                CommandType.StoredProcedure,
                "GetTodayKeywordsList",
                parms).Tables[0];
            nPageCount = Convert.ToInt32(parms[6].Value);
            nRecordCount = Convert.ToInt32(parms[7].Value);
            return dt;
        }

        /// <summary>
        /// 获取每天关键字收入情况
        /// </summary>
        /// <returns></returns>
        public DataTable GetKeywordsByDay(DateTime dBeginTime,DateTime dEndTime)
        {
            string strSql = @"select k.[ID],Count(k.[ID]) [DayCount],[LockNumber] from [Keywords] k 
                              left join [KeysRelation] kr on k.[ID]=kr.[KeywordsID] 
                              left join [Medical] m on kr.[MedicalID]=m.[ID] 
                              left join [Sort] s on k.[SortID]=s.[ID]
                              where kr.[PageIndex]=1 and [DayIndex]=1 and m.[Status]=1 and kr.[CreateTime] between @BeginTime and @EndTime group by k.[ID],[LockNumber]";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 获取所有关键字
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllList()
        {
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, "select * from [Keywords] order by [ID]").Tables[0];
        }
        /// <summary>
        /// 获取所有关键字
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllList1()
        {
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, "select * from [Keywords] order by [ID] desc").Tables[0];
        }

        /// <summary>
        /// 获取分析失败的关键字
        /// </summary>
        /// <returns></returns>
        public DataTable GetListBySeoResult()
        {
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, "select * from [Keywords] where [SeoResult]=0 order by [ID]").Tables[0];
        }


        /// <summary>
        /// 验证关键字是否存在
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public bool CheckKeywords(int nID, string strName)
        {
            object retVal = SqlHelper.ExecuteScalar(
                              SqlHelper.database,
                              CommandType.Text,
                              "select top 1 [ID] from [Keywords] where [Name]=@Name and (@ID=0 or [ID]<>@ID)",
                              new SqlParameter("@Name", strName),
                              new SqlParameter("@ID", nID));
            return retVal == null ? false : true;
        }

        /// <summary>
        /// 获取关键字
        /// </summary>
        /// <param name="nSortID"></param>
        /// <param name="strName"></param>
        /// <returns></returns>
        public DataTable GetKeywords(int nSortID, string strName)
        {
            string strSql = "select top 1 [ID],[SysLock],[DiyLock] from [Keywords] where [SortID]=@SortID and [Name]=@Name";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SortID", nSortID));
            parms.Add(new SqlParameter("@Name", strName));
            return SqlHelper.ExecuteDataset(
                                  SqlHelper.database,
                                  CommandType.Text,
                                  strSql,
                                  parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 获取关键字
        /// </summary>
        /// <param name="nSortID"></param>
        /// <returns></returns>
        public DataTable GetAllListByName()
        {
            string strSql = "select [ID],[Name],[SortID],[SysLock],[DiyLock] from [Keywords] order by len([Name]) desc;";
            return SqlHelper.ExecuteDataset(
                                  SqlHelper.database,
                                  CommandType.Text,
                                  strSql).Tables[0];
        }

        /// <summary>
        /// 按分类获取关键字
        /// </summary>
        /// <param name="nSortID"></param>
        /// <returns></returns>
        public DataTable GetListBySortID(int nSortID)
        {
            string strSql = "select [ID],[Name],[SysLock],[DiyLock] from [Keywords] where [SortID]=@SortID order by len([Name]) desc;";
            return SqlHelper.ExecuteDataset(
                                  SqlHelper.database,
                                  CommandType.Text,
                                  strSql,
                                  new SqlParameter("@SortID", nSortID)).Tables[0];
        }

        /// <summary>
        /// 获取关键字提成系数
        /// </summary>
        /// <param name="nID"></param>
        /// <returns></returns>
        public double GetScale(int nID)
        {
            object retVal = SqlHelper.ExecuteScalar(
                              SqlHelper.database,
                              CommandType.Text,
                              "select top 1 [Scale] from [Keywords] where [ID]=@ID",
                              new SqlParameter("@ID", nID));
            return retVal == null ? 0 : Convert.ToDouble(retVal);
        }

        /// <summary>
        /// 获取关键字报表
        /// </summary>
        /// <param name="strKeysList"></param>
        /// <param name="dBeginTime"></param>
        /// <param name="dEndTime"></param>
        /// <returns></returns>
        public DataTable GetKeywordsReport(string strKeysList, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = "select MedicalID,KeywordsID,count(KeywordsID) allcount from KeysRelation where [CreateTime] between @BeginTime and @EndTime and KeywordsID in("+strKeysList+") group by MedicalID,KeywordsID order by MedicalID,KeywordsID desc;";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                                  SqlHelper.database,
                                  CommandType.Text,
                                  strSql,
                                  parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 统计关键字
        /// </summary>
        /// <returns></returns>
        public int CountKeywords()
        {
            object retVal = SqlHelper.ExecuteScalar(
                              SqlHelper.database,
                              CommandType.Text,
                              "select count([ID]) from [Keywords]");
            return retVal == null ? 0 : Convert.ToInt32(retVal);
        }

        /// <summary>
        /// 设置关键字结果
        /// </summary>
        /// <param name="bSeoResult"></param>
        /// <returns></returns>
        public bool SetKeywordsResult(bool bSeoResult)
        {
            string strSql = "update [Keywords] set [SeoResult]=@SeoResult";
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, new SqlParameter("@SeoResult", bSeoResult)) > 0;
        }

        /// <summary>
        /// 设置关键字结果
        /// </summary>
        /// <param name="bSeoResult"></param>
        /// <returns></returns>
        public bool SetAllKeywordsResult()
        {
            string strSql = "update [Keywords] set [SeoResult]=1 where [SeoResult]=0";
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql) > 0;
        }

        /// <summary>
        /// 设置关键字结果
        /// </summary>
        /// <param name="bSeoResult"></param>
        /// <returns></returns>
        public bool SetKeywordsResult(int nID,bool bSeoResult)
        {
            string strSql = "update [Keywords] set [SeoResult]=@SeoResult where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SeoResult", bSeoResult));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }
    }
}
